/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.eng.scc.core.db;

import com.mysql.jdbc.Driver;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author rd
 */
public class Database
{

    private DateFormat dateFormat = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" );
    public static Database instance = null;
    public Connection con = null;
    
    private Database()
    {
    }

    public static Database getInstance()
    {
        if ( instance == null )
        {
            instance = new Database();
        }

        return instance;
    }

    public Connection getConnection() throws SQLException
    {
        if ( con == null || con.isClosed() )
        {
            DriverManager.registerDriver( new Driver() );
            
            con = DriverManager.getConnection( ConfigurationManager.getInstance().getProperty( "db.url", "" ), 
                                               ConfigurationManager.getInstance().getProperty( "db.usr", "" ), 
                                               ConfigurationManager.getInstance().getProperty( "db.pwd", "" ) );
        }
        
        return con;
    }

    public void release()
    {
        try
        {
//            if ( getConnection() != null )
            {
                getConnection().close();
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
        }
    }

    public <T> List<T> fechMany( String sql, Fetcher<T> f ) throws Exception
    {
        List<T> result = new ArrayList<T>();

        PreparedStatement ps = getConnection().prepareStatement( sql );

        if ( ps != null )
        {
            ResultSet rs = ps.executeQuery();

            if ( rs != null )
            {
                while ( rs.next() )
                {
                    result.add( f.fetch( rs ) );
                }

                rs.close();
            }

        }

        return result;
    }

    public <T> T fechOne( String sql, Fetcher<T> f ) throws Exception
    {
        PreparedStatement ps = getConnection().prepareStatement( sql );

        T result = null;

        if ( ps != null )
        {
            ResultSet rs = ps.executeQuery();

            while ( rs.next() )
            {
                result = f.fetch( rs );
            }

            rs.close();
        }

        return result;
    }

    public <T> void update( T value, Fetcher<T> f, String sql ) throws Exception
    {
        PreparedStatement ps = getConnection().prepareStatement( sql );
        f.update( value, ps );
        ps.execute();
        ps.close();
    }

    public <T> void insert( T value, Fetcher<T> f, String sql ) throws Exception
    {
        PreparedStatement ps = getConnection().prepareStatement( sql );
        f.insert( value, ps );
        ps.execute();
        ps.close();
    }

    public int nextID( String table ) throws Exception
    {
        int id = 1;
        ResultSet rs = null;

        try
        {

            String sql = "SELECT max(" + table + ".id) FROM " + table;

            rs = getResultSet( sql );

            if ( rs != null )
            {
                while ( rs.next() )
                {
                    id = rs.getInt( 1 ) + 1;
                }
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
        }
        finally
        {
            if ( rs != null )
            {
                rs.close();
            }
        }

        return id;
    }

    public void execute( String sql ) throws Exception
    {
        PreparedStatement ps = getConnection().prepareStatement( sql );

        try
        {
            ps.execute();
        }
        finally
        {
            ps.close();
        }
    }

    public ResultSet getResultSet( String sql ) throws Exception
    {
        return getConnection().prepareStatement( sql ).executeQuery();
    }

    public PreparedStatement getPreparedStatement( String sql ) throws Exception
    {
        return getConnection().prepareStatement( sql );
    }

    public String quote( String s )
    {
        return "'" + s + "'";
    }

    public String quote( boolean s )
    {
        return "'" + ( s ? "1" : "0" ) + "'";
    }

    public String quote( int s )
    {
        return "'" + s + "'";
    }

    public String quote( double s )
    {
        return "'" + s + "'";
    }

    public String quote( Date s )
    {
        return s != null ? "'" + s + "'" : null;
    }

    public String quote( Date s, boolean timeStamp )
    {
        return s != null ? "'" + dateFormat.format( s ) + "'" : null;
    }

    public String quote( Timestamp s )
    {
        return s != null ? "'" + s.toString() + "'" : null;
    }
}
